package org.jdbcparser.database;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.beanutils.DynaBean;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.ddlutils.Platform;
import org.apache.ddlutils.PlatformFactory;
import org.apache.ddlutils.model.Column;
import org.apache.ddlutils.model.Database;
import org.apache.ddlutils.model.Table;
import org.jdbcparser.rules.RulesColumn;
import org.jdbcparser.rules.RulesTable;

public class DatabaseHelper {

	private Platform platform;
	private String databaseName;
	private String driver;
	private String url;
	private String user;
	private String password;

	public DatabaseHelper(String databaseName, String driver, String url, String user, String password) {
		
		this.databaseName = databaseName;
		this.driver = driver;
		this.url = url;
		this.user = user;
		this.password = password;
		
		platform = PlatformFactory.createNewPlatformInstance(driver, url);
		
		platform.setUsername(user);
		platform.setPassword(password);
		
	}
	
	public void connect() {
		
		InternalDataSource dataSource = new InternalDataSource();
		
		dataSource.setDriverClassName(driver);
		dataSource.setUrl(url);
		dataSource.setUsername(user);
		dataSource.setPassword(password);
		
		disconnect(); // Close previous connection
		
		platform.setDataSource(dataSource);
		
	}
	
	public void disconnect() {
		
		InternalDataSource dataSource = (InternalDataSource) platform.getDataSource();
		
		if(dataSource != null) {
			try { dataSource.close(); } 
			catch (SQLException e) { }
		}
		
	}

	public void createTables(List<RulesTable> parsingTables) {
		
		Database database = platform.readModelFromDatabase(databaseName);
		List<Table> databaseTables = new ArrayList<Table>();
		
		for (RulesTable parsingTable : parsingTables) {
			if(database.findTable(parsingTable.getName()) == null) {
				databaseTables.add(toDatabaseTable(parsingTable));
			}
		}
		
		database.addTables(databaseTables);
		
		platform.alterTables(database, false);
		
	}
	
	public void dropTables(List<RulesTable> parsingTables) {
		
		Database database = platform.readModelFromDatabase(databaseName);
		List<Table> databaseTables = new ArrayList<Table>();

		for (RulesTable parsingTable : parsingTables) {
			if (database.findTable(parsingTable.getName()) != null) {
				database.removeTable(toDatabaseTable(parsingTable));
			}
		}

		platform.alterTables(database, false);
		
	}
	
	private Table toDatabaseTable(RulesTable parsingTable) {
		
		// FIXME VALIDATE PARSING TABLE 
		
		Table databaseTable = new Table();
		Column databaseColumn = null;
		
		databaseTable.setName(parsingTable.getName());
		databaseTable.setSchema(parsingTable.getSchema());
		
		for (RulesColumn parsingColumn : parsingTable.getColumns()) {
			
			String parsingColumType = parsingColumn.getType();
			int sizeStart = parsingColumType.indexOf('(');
			int sizeEnd = parsingColumType.indexOf(')');
			String databaseType = null;
			String databaseTypeSize = null;
			
			if( sizeStart != -1 && sizeEnd != -1) {
				databaseType = parsingColumType.substring(0, sizeStart);
				databaseTypeSize = parsingColumType.substring(sizeStart + 1, sizeEnd);
			} else {
				databaseType = parsingColumType;
			}
			
			databaseColumn = new Column();
			
			databaseColumn.setName(parsingColumn.getName());
			databaseColumn.setType(databaseType);
			
			if(databaseTypeSize != null) {
				databaseColumn.setSize(databaseTypeSize);
			}
			
			databaseTable.addColumn(databaseColumn);
		}
		
		return databaseTable;
	}

	public void insertRowSet(RowSet rowSet) {
		
		Database database = platform.readModelFromDatabase(databaseName);
		String sqlCommandDelimiter = platform.getPlatformInfo().getSqlCommandDelimiter();
		DynaBean databaseRow = database.createDynaBeanFor(rowSet.getTableName(), false);
		String[] columnOrdering = rowSet.getColumnOrderingArray();
		List<Object[]> rows = rowSet.getRows();

		StringBuilder insertSQLBatch = new StringBuilder();
		
		for (Object[] row : rows) {
			
			for(int i=0; i<row.length; i++) {
				databaseRow.set(columnOrdering[i], row[i]);
			}
			
			insertSQLBatch.append(platform.getInsertSql(database, databaseRow));
			insertSQLBatch.append(sqlCommandDelimiter);
		}
		
		platform.evaluateBatch(insertSQLBatch.toString(), false);
	}

	
	private class InternalDataSource extends BasicDataSource {
		public Connection getConnection(String username, String password) throws SQLException {
			super.setUsername(DatabaseHelper.this.user);
			super.setPassword(DatabaseHelper.this.password);
			return this.getConnection();
		}
	}

}
